import sqlite3
import akshare as ak
from datetime import datetime
import util
import pandas as pd

conn, cursor = util.getDb()


# 历史行情数据
def stock_zh_a_hist(symbol, start, end, stock_lhb_jgmmtj_em_df):
    start_str = start.strftime("%Y%m%d")
    end_str = end.strftime("%Y%m%d")
    # 查询数据库中已有的日期
    cursor.execute(
        "SELECT * FROM stock_daily WHERE symbol = ? AND date BETWEEN ? AND ?",
        (symbol, start, end),
    )
    rows = cursor.fetchall()
    existing_dates = {
        pd.to_datetime(row[1]).date() for row in rows
    }  # 使用集合存储已有日期

    # 获取全部日期范围内的数据
    date_range = pd.date_range(start=start, end=end).date

    # 过滤出缺失的日期
    missing_dates = [
        date
        for date in date_range
        if date not in existing_dates and date in util.trade_dates
    ]

    # 如果有缺失日期，则调用 Akshare 获取数据
    if missing_dates and end in missing_dates:
        stock_zh_a_hist_df = ak.stock_zh_a_hist(
            symbol=symbol,
            period="daily",
            start_date=start.strftime("%Y%m%d"),
            end_date=end.strftime("%Y%m%d"),
            adjust="qfq",
        )

        if "日期" in stock_zh_a_hist_df.columns:  # 转换数据类型

            stock_individual_fund_flow_df = ak.stock_individual_fund_flow(
                stock=symbol, market=util.get_market(symbol)
            )

            # 插入缺失数据
            for _, row in stock_zh_a_hist_df.iterrows():
                row_date = row["日期"]

                if row_date in missing_dates:
                    # 查找日期是否在 stock_individual_fund_flow_df 中
                    fund_row = stock_individual_fund_flow_df[
                        stock_individual_fund_flow_df["日期"] == row_date
                    ]

                    # 如果 fund_row 存在则提取数据，否则填充为空值
                    if not fund_row.empty:
                        main_net_inflow_amount = fund_row["主力净流入-净额"].values[0]
                        main_net_inflow_ratio = fund_row["主力净流入-净占比"].values[0]
                        super_large_net_inflow_amount = fund_row[
                            "超大单净流入-净额"
                        ].values[0]
                        super_large_net_inflow_ratio = fund_row[
                            "超大单净流入-净占比"
                        ].values[0]
                        large_net_inflow_amount = fund_row["大单净流入-净额"].values[0]
                        large_net_inflow_ratio = fund_row["大单净流入-净占比"].values[0]
                        medium_net_inflow_amount = fund_row["中单净流入-净额"].values[0]
                        medium_net_inflow_ratio = fund_row["中单净流入-净占比"].values[
                            0
                        ]
                        small_net_inflow_amount = fund_row["小单净流入-净额"].values[0]
                        small_net_inflow_ratio = fund_row["小单净流入-净占比"].values[0]
                    else:
                        main_net_inflow_amount = main_net_inflow_ratio = None
                        super_large_net_inflow_amount = super_large_net_inflow_ratio = (
                            None
                        )
                        large_net_inflow_amount = large_net_inflow_ratio = None
                        medium_net_inflow_amount = medium_net_inflow_ratio = None
                        small_net_inflow_amount = small_net_inflow_ratio = None

                    lhb_row = stock_lhb_jgmmtj_em_df[
                        stock_lhb_jgmmtj_em_df["上榜日期"] == row_date
                    ]
                    if not lhb_row.empty:
                        # 提取龙虎榜数据
                        buyer_institution_count = lhb_row["买方机构数"].iloc[0]
                        seller_institution_count = lhb_row["卖方机构数"].iloc[0]
                        institution_buy_amount = lhb_row["机构买入总额"].iloc[0]
                        institution_sell_amount = lhb_row["机构卖出总额"].iloc[0]
                        institution_net_buy_amount = lhb_row["机构买入净额"].iloc[0]
                        total_market_turnover = lhb_row["市场总成交额"].iloc[0]
                        institution_net_buy_ratio = lhb_row[
                            "机构净买额占总成交额比"
                        ].iloc[0]
                        lhb_reason = lhb_row["上榜原因"].iloc[0]
                    else:
                        # 使用默认值填充龙虎榜相关字段
                        buyer_institution_count = None
                        seller_institution_count = None
                        institution_buy_amount = None
                        institution_sell_amount = None
                        institution_net_buy_amount = None
                        total_market_turnover = None
                        institution_net_buy_ratio = None
                        lhb_reason = None
                    cursor.execute(
                        """
                    INSERT INTO stock_daily (
                        symbol, date, open, high, low, close, volume, amount,
                        turnover, amplitude, price_change_rate, price_change_amount,
                        main_net_inflow_amount, main_net_inflow_ratio, 
                        super_large_net_inflow_amount, super_large_net_inflow_ratio,
                        large_net_inflow_amount, large_net_inflow_ratio, 
                        medium_net_inflow_amount, medium_net_inflow_ratio,
                        small_net_inflow_amount, small_net_inflow_ratio,
                        buyer_institution_count, seller_institution_count,
                        institution_buy_amount, institution_sell_amount,
                        institution_net_buy_amount, total_market_turnover,
                        institution_net_buy_ratio, lhb_reason
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """,
                        (
                            row["股票代码"],  # symbol
                            row["日期"],  # date
                            row["开盘"],  # open
                            row["最高"],  # high
                            row["最低"],  # low
                            row["收盘"],  # close
                            row["成交量"],  # volume
                            row["成交额"],  # amount
                            row["换手率"],  # turnover
                            row["振幅"],  # amplitude
                            row["涨跌幅"],  # price_change_rate
                            row["涨跌额"],  # price_change_amount
                            main_net_inflow_amount,  # main_net_inflow_amount
                            main_net_inflow_ratio,  # main_net_inflow_ratio
                            super_large_net_inflow_amount,  # super_large_net_inflow_amount
                            super_large_net_inflow_ratio,  # super_large_net_inflow_ratio
                            large_net_inflow_amount,  # large_net_inflow_amount
                            large_net_inflow_ratio,  # large_net_inflow_ratio
                            medium_net_inflow_amount,  # medium_net_inflow_amount
                            medium_net_inflow_ratio,  # medium_net_inflow_ratio
                            small_net_inflow_amount,  # small_net_inflow_amount
                            small_net_inflow_ratio,  # small_net_inflow_ratio
                            buyer_institution_count,  # buyer_institution_count
                            seller_institution_count,  # seller_institution_count
                            institution_buy_amount,  # institution_buy_amount
                            institution_sell_amount,  # institution_sell_amount
                            institution_net_buy_amount,  # institution_net_buy_amount
                            total_market_turnover,  # total_market_turnover
                            institution_net_buy_ratio,  # institution_net_buy_ratio
                            lhb_reason,  # lhb_reason
                        ),
                    )
            conn.commit()
        cursor.execute(
            "SELECT * FROM stock_daily WHERE symbol = ? AND date BETWEEN ? AND ?",
            (symbol, start, end),
        )
        rows = cursor.fetchall()
        # Get column names from the cursor
    column_names = [description[0] for description in cursor.description]

    # Convert rows to a list of dictionaries
    data = [dict(zip(column_names, row)) for row in rows]
    return data


if __name__ == "__main__":
    cursor.execute("SELECT * FROM stock_info ")
    dbrows = cursor.fetchall()
    fromdate = util.get_trade_date(offset=-100)
    todate = util.get_trade_date()
    stock_lhb_jgmmtj_em_df = ak.stock_lhb_jgmmtj_em(
        start_date=fromdate.strftime("%Y%m%d"), end_date=todate.strftime("%Y%m%d")
    )
    for row in dbrows:
        stock_zh_a_hist(row[0], fromdate, todate, stock_lhb_jgmmtj_em_df)

    conn.close()
